package com.union.core.workModel.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.log4j.Logger;
import com.hnjowe.webdb.DBOperation;
import com.union.core.workModel.dao.ModelInfoDao;
import com.union.core.workModel.model.ModelInfo;

public class ModelInfoDaoImpl implements ModelInfoDao {
	private DBOperation opera;
	private ModelInfo modelInfo;
	private static Logger log = Logger.getLogger(ModelInfoDaoImpl.class.getName());
	@Override
	public int addModel(ModelInfo model) {
		int iid=0;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "insert into `model_for_work`(" +
					"`model_name`,`model_sex`,`model_birth`," +
					"`model_nation`,`model_native_place`," +
					"`model_political_status`,`model_education`," +
					"`model_work_unit`,`model_area`,`model_current`," +
					"`model_job`,`model_post`,`model_health`," +
					"`model_palm_year`,`model_level`,`model_honout_type`," +
					"`model_SFZH`,`model_bank_account`,`model_address`," +
					"`model_phone`,`model_cellphone`,`model_email`," +
					"`model_resume`,`model_deed`,`model_difficult`," +
					"`model_other`,model_image) values " +
					"(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			opera.prepStatment(sql);
			opera.setString(1, model.getModel_name());
			opera.setInt(2, model.getModel_sex());
			opera.setString(3, model.getModel_birth());
			opera.setString(4, model.getModel_nation());
			opera.setString(5, model.getModel_native_place());
			opera.setString(6, model.getModel_political_status());
			opera.setString(7, model.getModel_education());
			opera.setString(8, model.getModel_work_unit());
			opera.setString(9, model.getModel_area());
			opera.setString(10, model.getModel_current());
			opera.setString(11, model.getModel_job());
			opera.setString(12, model.getModel_post());
			opera.setString(13, model.getModel_health());
			opera.setString(14, model.getModel_palm_year());
			opera.setString(15, model.getModel_level());
			opera.setString(16, model.getModel_honout_type());
			opera.setString(17, model.getModel_SFZH());
			opera.setString(18, model.getModel_bank_account());
			opera.setString(19, model.getModel_address());
			opera.setString(20, model.getModel_phone());
			opera.setString(21, model.getModel_cellphone());
			opera.setString(22, model.getModel_email());
			opera.setString(23, model.getModel_resume());
			opera.setString(24, model.getModel_deed());
			opera.setString(25, model.getModel_difficult());
			opera.setString(26, model.getModel_other());
			opera.setString(27, model.getModel_image());
			opera.execute();
			String sql1="SELECT LAST_INSERT_ID()";
			opera.prepStatment(sql1);
			ResultSet rs= opera.executeQuery();
			if(rs.next()){
				iid=rs.getInt(1);
			}
			opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return iid;
		
	}

	@Override
	public boolean delModel(int modelId) {
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from model_for_work where model_id=?";
			opera.prepStatment(sql);
			opera.setInt(1, modelId);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	@Override
	public boolean editModel(ModelInfo model) {
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "update `model_for_work` set " +
					"`model_name`=?,`model_sex`=?," +
					"`model_birth`=?,`model_nation`=?," +
					"`model_native_place`=?,`model_political_status`=?," +
					"`model_education`=?,`model_work_unit`=?," +
					"`model_area`=?,`model_current`=?," +
					"`model_job`=?,`model_post`=?,`model_health`=?," +
					"`model_palm_year`=?,`model_level`=?," +
					"`model_honout_type`=?,`model_SFZH`=?," +
					"`model_bank_account`=?,`model_address`=?," +
					"`model_phone`=?,`model_cellphone`=?," +
					"`model_email`=?,`model_resume`=?," +
					"`model_deed`=?,`model_difficult`=?," +
					"`model_other`=?,model_image=? where `model_id`=?";
			opera.prepStatment(sql);
			opera.setString(1, model.getModel_name());
			opera.setInt(2, model.getModel_sex());
			opera.setString(3, model.getModel_birth());
			opera.setString(4, model.getModel_nation());
			opera.setString(5, model.getModel_native_place());
			opera.setString(6, model.getModel_political_status());
			opera.setString(7, model.getModel_education());
			opera.setString(8, model.getModel_work_unit());
			opera.setString(9, model.getModel_area());
			opera.setString(10, model.getModel_current());
			opera.setString(11, model.getModel_job());
			opera.setString(12, model.getModel_post());
			opera.setString(13, model.getModel_health());
			opera.setString(14, model.getModel_palm_year());
			opera.setString(15, model.getModel_level());
			opera.setString(16, model.getModel_honout_type());
			opera.setString(17, model.getModel_SFZH());
			opera.setString(18, model.getModel_bank_account());
			opera.setString(19, model.getModel_address());
			opera.setString(20, model.getModel_phone());
			opera.setString(21, model.getModel_cellphone());
			opera.setString(22, model.getModel_email());
			opera.setString(23, model.getModel_resume());
			opera.setString(24, model.getModel_deed());
			opera.setString(25, model.getModel_difficult());
			opera.setString(26, model.getModel_other());
			opera.setString(27, model.getModel_image());
			opera.setInt(28, model.getModel_id());
			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	@Override
	public ModelInfo queryModelById(int modelId) {
		ModelInfo modelInfo = null;
		try {
			opera = new DBOperation();
			opera.transcationBegin();

			String sql = "select model_id,model_name,model_sex,model_birth," +
					"model_nation,model_native_place," +
					"model_political_status,model_education," +
					"model_work_unit,model_area,model_current," +
					"model_job,model_post,model_health," +
					"model_palm_year,model_level,model_honout_type," +
					"model_SFZH,model_bank_account,model_address," +
					"model_phone,model_cellphone,model_email," +
					"model_resume,model_deed,model_difficult," +
					"model_other,model_image "
					+ "from model_for_work where  model_id=?";
			opera.prepStatment(sql);
			opera.setInt(1, modelId);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				modelInfo = new ModelInfo();
				modelInfo.setModel_id(res.getInt(1));
				modelInfo.setModel_name(res.getString(2));
				modelInfo.setModel_sex(res.getInt(3));
				modelInfo.setModel_birth(res.getString(4));
				modelInfo.setModel_nation(res.getString(5));
				modelInfo.setModel_native_place(res.getString(6));
				modelInfo.setModel_political_status(res.getString(7));
				modelInfo.setModel_education(res.getString(8));
				modelInfo.setModel_work_unit(res.getString(9));
				modelInfo.setModel_area(res.getString(10));
				modelInfo.setModel_current(res.getString(11));
				modelInfo.setModel_job(res.getString(12));
				modelInfo.setModel_post(res.getString(13));
				modelInfo.setModel_health(res.getString(14));
				modelInfo.setModel_palm_year(res.getString(15));
				modelInfo.setModel_level(res.getString(16));
				modelInfo.setModel_honout_type(res.getString(17));
				modelInfo.setModel_SFZH(res.getString(18));
				modelInfo.setModel_bank_account(res.getString(19));
				modelInfo.setModel_address(res.getString(20));
				modelInfo.setModel_phone(res.getString(21));
				modelInfo.setModel_cellphone(res.getString(22));
				modelInfo.setModel_email(res.getString(23));
				modelInfo.setModel_resume(res.getString(24));
				modelInfo.setModel_deed(res.getString(25));
				modelInfo.setModel_difficult(res.getString(26));
				modelInfo.setModel_other(res.getString(27));
				modelInfo.setModel_image(res.getString(28));
				int age=getAge(res.getString(4));
				modelInfo.setAge(age);
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return modelInfo;
	}

	@Override
	public List<ModelInfo> queryModelList(int sex, String honour,
			String level, String current, String paramName, String paramValue,
			String orderByName, int start, int intPageSize) {
		
		String sqlstr1 = ""; // 条件1
		String sqlstr2 = ""; // 条件2
		String sqlstr3 = ""; // 条件3
		String sqlstr4 = ""; // 条件4
		String sqlstr5 = ""; // 条件4
		String orderstr="";
		if (sex != 0)sqlstr1 = " and model_sex="+sex;
		if (level !=null)sqlstr2 = " and model_level='"+level+"'";
		if (current !=null)sqlstr3 = " and model_current='"+current+"'";
		if (honour !=null)sqlstr5 = " and model_honout_type='"+honour+"'";
		if (paramName != null && paramValue != null) {
			if(paramName.equals("name"))
				sqlstr4 = " and model_name like '%"+paramValue+"%'";
			if(paramName.equals("unit"))
				sqlstr4 = " and model_work_unit like '%"+paramValue+"%'";
			if (paramName.equals("area"))
				sqlstr4 = " and model_area like '%" + paramValue + "%'";
			if (paramName.equals("address"))
				sqlstr4 = " and model_address like '%" + paramValue + "%'";
		}
//		if (startDate != null && endDate != null){
//			sqlstr3 = " and DATE between DATE_FORMAT('"+startDate+"','%Y-%m-%d') and DATE_FORMAT('"+endDate+"','%Y-%m-%d') ";
//		}
		if (orderByName != null&& !"".equals(orderByName)) {
			String str[] = orderByName.split("-");			
			if (str[1].equals("UP"))
				orderstr = " order by " + str[0];
			if (str[1].equals("DOWN"))
				orderstr = " order by " + str[0] + " desc";
		}
		List<ModelInfo> modelList = new ArrayList<ModelInfo>();
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
					+ "model_id,model_name,model_work_unit,model_area,model_sex,model_birth," +
							"model_political_status,model_job,model_address," 
					+"model_phone,model_cellphone,model_honout_type,model_level,model_current "
					+ "from model_for_work "
					+ "where model_id>0 "
					+ sqlstr1+ sqlstr2+ sqlstr3+sqlstr4+sqlstr5
					+ orderstr
					+ " LIMIT ?,?";
			opera.prepStatment(sql);
			opera.setInt(1, start);
			opera.setInt(2, intPageSize);
			ResultSet res = opera.executeQuery();// 执行上面语句
			int i = 0;
			
			while (res.next()) {
				modelInfo = new ModelInfo();
				modelInfo.setModel_id(res.getInt(1));
				modelInfo.setModel_name(res.getString(2));
				modelInfo.setModel_work_unit(res.getString(3));
				modelInfo.setModel_area(res.getString(4));
				modelInfo.setModel_sex(res.getInt(5));
				modelInfo.setModel_birth(res.getString(6));
				modelInfo.setModel_political_status(res.getString(7));
				modelInfo.setModel_job(res.getString(8));
				modelInfo.setModel_address(res.getString(9));
				modelInfo.setModel_phone(res.getString(10));
				modelInfo.setModel_cellphone(res.getString(11));
				modelInfo.setModel_honout_type(res.getString(12));
				modelInfo.setModel_level(res.getString(13));
				modelInfo.setModel_current(res.getString(14));
				int age=getAge(res.getString(6));
				modelInfo.setAge(age);
				modelList.add(i, modelInfo);
				modelInfo=null;
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return modelList;
	}

	@Override
	public int queryModelListCount(int sex, String honour, String level,
			String current, String paramName, String paramValue) {
		int totalSize = 0;// 某条件查询下记录的总条数
		String sqlstr1 = ""; // 条件1
		String sqlstr2 = ""; // 条件2
		String sqlstr3 = ""; // 条件3
		String sqlstr4 = ""; // 条件4
		String sqlstr5 = ""; // 条件5
		if (sex != 0)sqlstr1 = " and model_sex="+sex;
		if (level !=null)sqlstr2 = " and model_level='"+level+"'";
		if (current !=null)sqlstr3 = " and model_current='"+current+"'";
		if (honour !=null)sqlstr5 = " and model_honout_type='"+honour+"'";
		if (paramName != null && paramValue != null) {
			if(paramName.equals("name"))
				sqlstr4 = " and model_name like '%"+paramValue+"%'";
			if(paramName.equals("unit"))
				sqlstr4 = " and model_work_unit like '%"+paramValue+"%'";
			if (paramName.equals("area"))
				sqlstr4 = " and model_area like '%" + paramValue + "%'";
			if (paramName.equals("address"))
				sqlstr4 = " and model_address like '%" + paramValue + "%'";
		}
//		if (startDate != null && endDate != null){
//			sqlstr3 = " and DATE between DATE_FORMAT('"+startDate+"','%Y-%m-%d') and DATE_FORMAT('"+endDate+"','%Y-%m-%d') ";
//		}
		try{
			opera = new DBOperation();
			opera.transcationBegin();			
			String sql = "select count(*) as totalSize "
					+ "from model_for_work "
					+ "where model_id>0 "
					+ sqlstr1+ sqlstr2+sqlstr3+sqlstr4+sqlstr5;
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				totalSize = res.getInt(1);
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return totalSize;
	}
	//计算年龄
	private int getAge(String ss){
		//获取当前日期
		Calendar ca = Calendar.getInstance();
	     int y1 = ca.get(Calendar.YEAR);//获取年份
	     int m1=ca.get(Calendar.MONTH);//获取月份 
			//计算年龄
			String[]mb=ss.split("-");
			int y2=Integer.parseInt(mb[0]);
			int m2=Integer.parseInt(mb[1]);
			int age=y1-y2;
			if(m1>m2)age+=1;
			System.out.println("y1="+y1+";m1="+m1+";y2="+y2+";m2="+m2+";now="+ca);
			return age;
	}
}
